package com.ujn.demo.use;

import com.alibaba.fastjson.JSON;
import com.ujn.demo.model.ClockTime;
import com.ujn.demo.model.StaffModel;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

import static com.ujn.demo.common.CommonUtils.*;

/**
 * 职员员工工时计算
 * 规则：
 * 工作日：
 * 1.上班时间 8:30-18:00
 *
 * 周末：
 * 1.周六上半天
 * 2.周日不上班
 *
 * 备注：
 * 1. 半小时之内迟到不扣工时，半小时之后扣，不足半小时则取半小时计算，如迟到32分钟，扣1小时工时
 * 2. 3月27日周六是正常上班
 * 3. 周六上班的第二个打卡时间超过14:00，显示异常
 * 4. 职员郑小兰是否需要计算加班？
 * 5. 有一些人上班时间是9：30-19:00；有一些是8:00:-17：30，这些规则都没有过滤掉。
 */
public class CalculateStaffWorkTime {

    private static final Logger LOGGER = LoggerFactory.getLogger(CalculateStaffWorkTime.class);

    /**
     * excel地址
     */
    private static final String folderPath = "C:\\Users\\可爱的小宝贝\\Desktop\\身份证\\0405";

    private static final String filePath = folderPath + "\\职员.xls";

    private static Map<Integer, StaffModel> map = new HashMap<>();

    public static void main(String[] args) throws Exception {
        //读取excel
        readExcel();
        //写入excel
        writeExcel();
    }

    /**
     * 写excel
     * @throws IOException
     */
    public static void writeExcel() throws IOException {
        try{
            // 创建工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet("sheet1");

            //设置标头
            HSSFRow zeroRows = sheet.createRow(0);
            zeroRows.createCell(0).setCellValue("考勤号码");
            zeroRows.createCell(1).setCellValue("姓名");
            zeroRows.createCell(2).setCellValue("部门");
            zeroRows.createCell(3).setCellValue("日期");
            zeroRows.createCell(4).setCellValue("时间");
            zeroRows.createCell(5).setCellValue("打卡1");
            zeroRows.createCell(6).setCellValue("打卡2");
            zeroRows.createCell(7).setCellValue("异常:1 异常标记；2 迟到标记；3-九点半上班");
            zeroRows.createCell(8).setCellValue("正班工时");
            zeroRows.createCell(9).setCellValue("迟到时间");

            //row 行数
            for (int row = 1; row <= map.size(); row++) {
                HSSFRow rows = sheet.createRow(row);
                //获取staffModel, clo列数
                StaffModel staffModel = map.get(row);
                ClockTime clockTime = staffModel.getClockTime();
                for (int col = 0; col < 10; col++) {
                    if (col == 0){
                        rows.createCell(col).setCellValue(staffModel.getNumber());
                    }else if (col == 1){
                        rows.createCell(col).setCellValue(staffModel.getName());
                    }else if (col == 2){
                        rows.createCell(col).setCellValue(staffModel.getDepartment());
                    }else if (col == 3){
                        rows.createCell(col).setCellValue(staffModel.getDateTime());
                    }else if (col == 4){
                        rows.createCell(col).setCellValue(staffModel.getTime());
                    }else if (col == 5 && Objects.nonNull(clockTime)){
                        String time = StringUtils.isEmpty(clockTime.getFirstTime())?"":clockTime.getFirstTime();
                        rows.createCell(col).setCellValue(time);
                    }else if (col == 6 && Objects.nonNull(clockTime)){
                        String time = StringUtils.isEmpty(clockTime.getSecondTime())?"":clockTime.getSecondTime();
                        rows.createCell(col).setCellValue(time);
                    }else if (col == 7 && Objects.nonNull(staffModel.getIsException()) && !staffModel.getIsException().equals("0")){
                        rows.createCell(col).setCellValue(staffModel.getIsException());
                    }else if (col == 8 && Objects.nonNull(staffModel.getNormalHours())){
                        rows.createCell(col).setCellValue(staffModel.getNormalHours());
                    }else if (col == 9 && Objects.nonNull(staffModel.getLateMinutes()) && staffModel.getLateMinutes()!=0){
                        rows.createCell(col).setCellValue(staffModel.getLateMinutes());
                    }
                }
            }
            String []str = filePath.split("\\.");
            String createFile = str[0] + "-" + getTime() + "." + str[1];
            File xlsFile = new File(createFile);
            xlsFile.createNewFile();
            FileOutputStream xlsStream = new FileOutputStream(xlsFile);
            workbook.write(xlsStream);
            LOGGER.info("writeExcel，计算职员工时，请查看excel位置：{}", xlsFile.getAbsolutePath());
        }catch (Exception e){
            LOGGER.error("writeExcel，错误：{}", e.getMessage());
        }
    }

    /**
     * 读取excel
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void readExcel() throws Exception {
        File xlsFile = new File(filePath);
        // 获得工作簿
        Workbook workbook = WorkbookFactory.create(xlsFile);
        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();
        int normalCount = 0;
        // 遍历工作表
        for (int i = 0; i < sheetCount; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 获得行数
            int rows = sheet.getLastRowNum() + 1;
            // 获得列数，先获得一行，再得到该行列数
            Row tmp = sheet.getRow(0);
            if (tmp == null) {
                continue;
            }
            // 读取数据
            for (int row = 1; row < rows; row++) {//获取第几行
                Row r = sheet.getRow(row);
                StaffModel staffModel = new StaffModel();
                for (int col = 0; col < 5; col++) {//获取列数
                    try {
                        String str = r.getCell(col).getStringCellValue();
                        if (col == 0){
                            staffModel.setNumber(str);
                        }else if (col == 1){
                            staffModel.setName(str);
                        }else if (col == 2){
                            staffModel.setDepartment(str);
                        }else if (col == 3){
                            staffModel.setDateTime(str);
                        }else if (col == 4){
                            staffModel.setTime(str);
                        }
                        map.put(row, staffModel);
                    }catch (Exception e){
                        LOGGER.error("出错：{}, 编号：{}, staffModel:{}", e.getMessage(), row, JSON.toJSON(staffModel));
                    }
                }
                LOGGER.info("输出row:{},staffModel:{}", row, JSON.toJSON(staffModel));
                //判断是否周末
                String dateTime = staffModel.getDateTime();
                int dayForWeek = dayForWeek(dateTime);
                if (dayForWeek>=1 && dayForWeek <=6){
                    //周六标记
                    if(dayForWeek == 6){
                        String str = dateTime + "-星期六";
                        if (staffModel.getDateTime().equals("2021/3/27")){
                            str += "-补班";
                        }
                        staffModel.setDateTime(str);
                    }

                    //工作日、转化时间
                    if (StringUtils.isEmpty(staffModel.getTime())){
                        //异常为1
                        staffModel.setIsException("1-打卡异常");
                    }else{
                        ClockTime clockTime = sortClockTime(staffModel.getTime());
                        if (Objects.isNull(clockTime) || StringUtils.isEmpty(clockTime.getFirstTime()) || StringUtils.isEmpty(clockTime.getSecondTime()) ){
                            //异常为1
                            staffModel.setIsException("1");
                        }else{
                            staffModel.setClockTime(clockTime);
                            normalCount++;
                            //正常为0
                            String isException = "0";
                            //判断是否周六(3月27号要补班，上一整天的班)
                            boolean isWeekend = dayForWeek == 6 && !staffModel.getDateTime().equals("2021/3/27-星期六-补班");
                            //迟到几分钟
                            int lateMinutes = calculateLateMinutes(clockTime, isWeekend);
                            float lateHours = 0f;
                            if (lateMinutes > 30f){
                                //计算迟到的时间
                                lateHours = (float) Math.ceil(new BigDecimal(String.valueOf(lateMinutes/30f)).doubleValue())*0.5f;
                                //判断上班时间是否是9:30-19:00，是的话。lateMinutes=0，isException = "3-九点半上班"
                                if (callTime(clockTime.getFirstTime(), "09:30") <= 0 && callTime(clockTime.getFirstTime(), "09:30") <= 0){
                                    lateMinutes = 0;
                                    isException="3-九点半上班";
                                    lateHours = 0;
                                }
                            }
                            //计算正常工时
                            if (isWeekend){
                                staffModel.setNormalHours(4.0f-lateHours);
                                //周末的时候，第二个超过14:00就输入异常
                                if (callTime(clockTime.getSecondTime(), "14:00")>=0){
                                    isException="1-周六打卡超过14:00";
                                }
                            }else{
                                staffModel.setNormalHours(8.0f-lateHours);
                            }
                            if (lateMinutes > 0){
                                //迟到isException为2
                                staffModel.setLateMinutes(lateMinutes);
                                isException = "2-迟到";
                            }
                            staffModel.setIsException(isException);
                        }
                    }
                }else if(dayForWeek == 7){
                    staffModel.setDateTime(dateTime + "-星期日");
                }
            }
            LOGGER.info("正常条数：{}，输出map:{}", normalCount, JSON.toJSON(map));
        }
    }

    /**
     * 计算正常上班工时
     * @param clockTime
     * @return
     */
    public static float calculateNormalHours(ClockTime clockTime, boolean isWeekend){
        //工作日正常：上午：8:30-18:00 570分钟 8工时；周六：8:30-12:00 4工时
        float normalTime = (float) callTime(clockTime.getSecondTime(), clockTime.getFirstTime());
        float normalHours = 0f;
        float workTime = 540f;//工作日有三十分钟缓冲时间不算迟到 570-30
        if (isWeekend){
            workTime = 180f;//工作日有三十分钟缓冲时间不算迟到 210-30
        }
        if (normalTime >= workTime){
            if (!isWeekend){
                normalHours += 8f;
            }else{
                normalHours += 4f;
            }
        }else{
            if (!isWeekend){
                float getFloatHours = (normalTime-90f)/60f;//8:30-18:00  -> 9.5 - 1.8 = 8
                int getIntHour = (int)getFloatHours;
                if (getFloatHours > (getIntHour + 0.5)){
                    normalHours += (getIntHour + 0.5);
                }else{
                    normalHours += getIntHour;
                }
            }else{
                float getFloatHours = (normalTime+30f)/60f;//8:30-12:00  算四个工时-> 240
                int getIntHour = (int)getFloatHours;
                if (getFloatHours > (getIntHour + 0.5)){
                    normalHours += (getIntHour + 0.5);
                }else{
                    normalHours += getIntHour;
                }
            }
        }
        return normalHours;
    }

    @Test
    public void test(){
        float opt = (float) Math.ceil(new BigDecimal(String.valueOf(32/30f)).doubleValue())*0.5f;
        System.out.println(opt);
    }

    /**
     * 计算迟到分钟数
     * @param clockTime
     * @return
     */
    public static int calculateLateMinutes(ClockTime clockTime, boolean isWeekend){
        int lateTime = 0;
        //算出第一个卡 time1为正则加为负则正常
        int time1 = callTime(clockTime.getFirstTime(), "08:30");
        int time2 = 0;
        //周六上半天班：8:30-12:00；工作日的话，是8:30-18:00
        if (isWeekend){
            callTime("12:00", clockTime.getSecondTime());
        }else{
            callTime("18:00", clockTime.getSecondTime());
        }
        if (time1 > 0){
            lateTime+=time1;
        }else if (time2 > 0){
            lateTime+=time2;
        }
        return lateTime;
    }



    /**
     * 整理好两个打卡时间
     * @param time
     * @return
     */
    public static ClockTime sortClockTime(String time){
        //转化时间
        String[] splitTime = time.split(" ");
        int timeLength = splitTime.length;
        //分割后长度少于2个直接返回空
        if (timeLength < 2){
            return null;
        }
        List<String> list1 = new ArrayList<>();
        List<String> list2 = new ArrayList<>();
        for (int i=0; i<timeLength; i++){
            int hourOrMinute = getHourOrMinute(splitTime[i], true);
            //第一个打卡 早上6-11点的归一类
            if (hourOrMinute>=6 && hourOrMinute<11){
                list1.add(splitTime[i]);
            }else if(hourOrMinute>=11 && hourOrMinute<24){
                //第二个打卡，下午17-24点的归一类
                list2.add(splitTime[i]);
            }
        }
        if (list1.size() == 0  || list2.size() == 0){
            return null;
        }
        //把四个list转换成ClockTime
        ClockTime clockTime = listToClockTime(list1, list2);
        return clockTime;
    }

    /**
     * 把list转化为ClockTime实体
     * @param list1
     * @param list2
     * @return
     */
    private static ClockTime listToClockTime(List<String> list1, List<String> list2){
        ClockTime clockTime = new ClockTime();
        //不满足打卡的个数，直接返回空值；规则：周一-周五必须上满8个小时，周六周天不强制
        if (list1.size() == 0  || list2.size() == 0){
            return null;
        }
        clockTime.setFirstTime(list1.get(0));
        clockTime.setSecondTime(list2.get(list2.size()-1));//取第一个值
        LOGGER.info("listToClockTime,clockTime:{}",  JSON.toJSON(clockTime));
        return clockTime;
    }
}
